package com.xunfang.liu;

import com.xunfang.bean.Emp;
import com.xunfang.dao.EmpDaoimpt;
import com.xunfang.service.EmpService;
import com.xunfang.service.EmpServiceImpt;
import com.xunfang.utils.DBManager;

import java.sql.*;
import java.util.ArrayList;
import java.sql.Date;
import java.util.List;
import java.util.Scanner;

public class TestJDBC {
    public static void t1() throws Exception {
        //1.类加载的方式-加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2.创建数据库链接
        //参数1：链接数据库的url  jdbc:mysql://ip地址:3306/student?characterEncoding=utf8
        //参数2：用户名
        //参数3：密码
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?characterEncoding=utf8", "root", "0000");
        System.out.println(conn);
        //3.创建statement对象，发送sql语句
        Statement state = conn.createStatement();
        //4.创建sql语句
        String sql = " insert into emp (empno,ename,job) values (8888,'我操你妈','CLERK') ";
        String update = "update emp set ename = '我是你爹' where empno = 8888";
        String delete = "delete from emp where empno = 8888";
        //5.执行SQL语句
        //executeUpdate() : 执行增删改的操作
        //返回值表示：操作影响的行数
        int i = state.executeUpdate(delete);
        System.out.println("i = " + i);
        //6.关闭连接，释放资源
        state.close();
        conn.close();
    }

    public static void tt1() throws Exception {
        //1.类加载的方式-加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2.创建数据库链接
        //参数1：链接数据库的url  jdbc:mysql://ip地址:3306/student?characterEncoding=utf8
        //参数2：用户名
        //参数3：密码
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?characterEncoding=utf8","root","0000");
        //3.创建statement对象，发送sql语句
        Statement state = conn.createStatement();
        //4.创建sql语句
        String sql = "delete from emp where empno = 8888";
        //5.执行SQL语句
        //executeUpdate() : 执行增删改的操作
        //返回值表示：操作影响的行数
        int i = state.executeUpdate(sql);
        System.out.println("i = " + i);
        //6.关闭连接，释放资源
        state.close();
        conn.close();
    }

    //查询
    public static void t3() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?characterEncoding=utf8", "root", "0000");
        //创建执行sql语句的对象
        Statement state = conn.createStatement();
        String sql = "select * from emp";
        //结果集对象
        ResultSet rs = state.executeQuery(sql);
        //调用rs的next方法找到下一行记录
        rs.next();
        /*
        int          int
        varchar      String
        decimal      double
        float        float
        date         date

        字段值获取方式：
            1.get(字段的索引位置<从1开始>)
            2.get(字段的名称<如果使用了别名，写别名>)
         */
        while (rs.next()) {
            System.out.println(rs.getInt(1) + "   " + rs.getString(2) + "   "
                    + rs.getString(3) + "  " + rs.getInt(4) + "   " + rs.getDate(5)
                    + "   " + rs.getFloat(6) + "   " + rs.getFloat(7) + "  " + rs.getInt(8));
        }
        state.close();
        conn.close();
    }

    public static void tt3() throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?characterEncoding=utf8","root","0000");
        Statement state = conn.createStatement();
        String sql = " select * from emp ";
        ResultSet rs = state.executeQuery(sql);
        List<Emp> list = new ArrayList<>();
        while (rs.next()){
            Emp emp = new Emp();
            emp.setEmpno(rs.getInt(1));
            emp.setEname(rs.getString(2));
            emp.setJob(rs.getString(3));
            emp.setMgr(rs.getInt(4));
            emp.setHireDate(rs.getDate(5));
            emp.setSal(rs.getDouble(6));
            emp.setComm(rs.getDouble(7));
            emp.setDeptno(rs.getInt(8));
            list.add(emp);
        }
        list.forEach(System.out::println);
        DBManager.closeAll(conn,state,rs);
    }



    public static void t4() throws Exception {
        Connection conn = DBManager.getConn();
        Statement state = conn.createStatement();
        String sql = "select * from emp";
        ResultSet rs = state.executeQuery(sql);
        while (rs.next()) {
            System.out.println(rs.getInt(1) + "   " + rs.getString(2) + "   "
                    + rs.getString(3) + "  " + rs.getInt(4) + "   " + rs.getDate(5)
                    + "   " + rs.getFloat(6) + "   " + rs.getFloat(7) + "  " + rs.getInt(8));
        }
        DBManager.closeAll(conn, state, rs);
    }


    //增删改
    public static void ex(String sql) throws Exception {
        Connection conn = DBManager.getConn();
        Statement state = conn.createStatement();
        int i = state.executeUpdate(sql);
        System.out.println("i = " + i);
        DBManager.closeAll(conn, state, null);
    }

    public static void t5() throws Exception {
        String sql = "delete from emp where empno = 7777";
        ex(sql);
    }

    public static void t6() throws Exception {
        Scanner sc = new Scanner(System.in);
        System.out.print("请输入账号:");
        String ad = sc.next();
        System.out.print("请输入密码:");
        String pw = sc.next();
        Connection conn = DBManager.getConn();
        Statement state = conn.createStatement();
        String sql = "select * from users where username = '" + ad + "' and usepwd = '" + pw + "'";
        ResultSet rs = state.executeQuery(sql);
        if (rs.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
    }

    //预编译(防止更改格式)
    public static void t7() throws Exception {
        Scanner sc = new Scanner(System.in);
        System.out.print("请输入账号:");
        String ad = sc.next();
        System.out.print("请输入密码:");
        String pw = sc.next();
        Connection conn = DBManager.getConn();
        Statement state = conn.createStatement();
        String sql = "select * from users where username = ? and usepwd = ? ";
        //prepareStatement:预编译对象（操作数据的对象）
        PreparedStatement ps = conn.prepareStatement(sql);
        //给占位符设值
        //语法：ps.set数据类型（参数的设置<从1开始，参数值>）;
        ps.setString(1, ad);
        ps.setString(2, pw);
        //这里将不在传参数值
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
    }

    //javabean把数据保存起来
    public static void t8() throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * from emp";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        List<Emp> list = new ArrayList<>();
        while (rs.next()) {
            //创建Emp对象 - DBUtils框架
            Emp emp = new Emp();
            emp.setEmpno(rs.getInt(1));
            emp.setEname(rs.getString(2));
            emp.setJob(rs.getString(3));
            emp.setMgr(rs.getInt(4));
            emp.setHireDate(rs.getDate(5));
            emp.setSal(rs.getDouble(6));
            emp.setComm(rs.getDouble(7));
            emp.setDeptno(rs.getInt(8));
            list.add(emp);
        }
        DBManager.closeAll(conn,ps,rs);
        //遍历集合
        list.forEach(System.out::println);
    }


    //测试dao插入
    public static void t9()throws Exception{
        EmpDaoimpt empdao = new EmpDaoimpt();
        Emp emp = new Emp();
        emp.setEmpno(9988);
        emp.setHireDate(Date.valueOf("2022-01-01"));
        emp.setEname("赵云");
        emp.setJob("SALESMAN");
        emp.setSal(88888.0);
        emp.setMgr(7788);
        emp.setDeptno(30);
        emp.setComm(0.0);
        int i = empdao.insertEmp(emp);
        System.out.println(i);

    }

    //测试dao修改
    public static void t10() throws Exception{
        EmpDaoimpt empdao = new EmpDaoimpt();
        Emp emp = new Emp();
        emp.setEname("关羽");
        emp.setSal(8888.8);
        emp.setEmpno(9999);
        int i = empdao.updateEmp(emp);
        System.out.println(i);
    }

    //测试dao删除
    public static void t11() throws Exception{
        EmpDaoimpt empdao = new EmpDaoimpt();
        int i = empdao.deleteEmp(8888);
        System.out.println(i);
    }

    //测试全查询
    public static void t12() throws Exception{
        EmpDaoimpt empdao = new EmpDaoimpt();
        List<Emp> list = empdao.selectAllEmp();
        list.forEach(System.out::println);
    }

    //测试动态查询
    public static void t13() throws Exception{
        List<Emp> list = empservice.selectEmp(null, "adc", 8888.8);
        list.forEach(System.out::println);
    }

    //测试动态查询第二遍
    public static void moveselect() throws Exception{
        EmpServiceImpt emps = new EmpServiceImpt();
        List<Emp> list = emps.moveselect(null, "adc", null);
        list.forEach(System.out::println);
    }


    //测试删除
    public static void t14() throws Exception{
        boolean b = empservice.delEmpAndDeptByDeptno(10);
        if (b){
            System.out.println("删除成功!");
        }else {
            System.out.println("删除失败");
        }
    }


    static EmpService empservice = new EmpServiceImpt();
    //主方法
    public static void main(String[] args) throws Exception {
        moveselect();
    }
}
